﻿Imports QL_GioiThieu_NhaThue.sqlDataProvider
Imports System.Data.SqlClient

Public Class TT_ThueNhaDAO
   Public Shared Function GetAll() As DataTable
        Dim listTTThueNha As New DataTable()
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "LayTTThueNha"
            cmd.Connection = cn

            Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
            da.Fill(listTTThueNha)
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

        Return listTTThueNha
    End Function

    Public Shared Function GetByID(ByVal sqlParameters As SqlParameter()) As List(Of TT_ThueNhaDTO)
        Dim listTT_ThueNha As New List(Of TT_ThueNhaDTO)
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Dim cmd As New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = ""    'Bo sung ten store 
        cmd.Connection = cn
        If Not IsNothing(sqlParameters) Then
            cmd.Parameters.AddRange(sqlParameters)
        End If
        cn.Open()

        Dim rd As SqlDataReader = cmd.ExecuteReader()
        While rd.Read()
            Dim tt_thuenha As TT_ThueNhaDTO = New TT_ThueNhaDTO()
            tt_thuenha.iMaTTThueNha = rd.GetInt32(0)
            tt_thuenha.strMaKhachThue = rd.GetString(1)
            tt_thuenha.strMaNha = rd.GetString(2)
            tt_thuenha.dNgayThue = rd.GetDateTime(3)
            tt_thuenha.iGia = rd.GetInt32(4)

            listTT_ThueNha.Add(tt_thuenha)
        End While
        cn.Close()

        Return listTT_ThueNha
    End Function

    Public Shared Sub Insert(ByVal sqlParameters As SqlParameter())
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "usp_InsertTtThuenha"
            cmd.Connection = cn
            cmd.Parameters.AddRange(sqlParameters)
            cn.Open()
            cmd.ExecuteNonQuery()
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

    End Sub

    Public Shared Sub Delete(ByVal paraID As SqlParameter)
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "usp_DeleteTtThuenha"
            cmd.Connection = cn

            cmd.Parameters.Add(paraID)
            cn.Open()
            cmd.ExecuteNonQuery()
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

    End Sub

    Public Shared Sub Update(ByVal sqlParameters As SqlParameter())
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "usp_InsertTtThuenha"
            cmd.Connection = cn

            cmd.Parameters.AddRange(sqlParameters)
            cn.Open()
            cmd.ExecuteNonQuery()
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

    End Sub

    Public Shared Function TimKiemTTTheoQuan(ByVal paraMaQuan As SqlParameter) As DataTable
        Dim listTT As New DataTable()
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "TimKiemTTThuetheoQuan"
            cmd.Connection = cn
            cmd.Parameters.Add(paraMaQuan)

            Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
            da.Fill(listTT)
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

        Return listTT
    End Function

    Public Shared Function TimKiemTTTheoGia(ByVal paraGia As SqlParameter) As DataTable
        Dim listTT As New DataTable()
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "TimKiemTTThuetheoGia"
            cmd.Connection = cn
            cmd.Parameters.Add(paraGia)

            Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
            da.Fill(listTT)
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

        Return listTT
    End Function

    Public Shared Function TimKiemTTTheoGia_Quan(ByVal paraMaQuan As SqlParameter, ByVal paraGia As SqlParameter) As DataTable
        Dim listTT As New DataTable()
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "TimKiemTTThuetheoGia_Quan"
            cmd.Connection = cn
            cmd.Parameters.Add(paraMaQuan)
            cmd.Parameters.Add(paraGia)

            Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
            da.Fill(listTT)
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

        Return listTT
    End Function
End Class